

package MaintMenu.VenPricingUpdTBLN;

import javax.swing.*;
import java.sql.*;

public class TYGtxtbl {

    public TYGtxtbl(int z)    {  //0 = partlink ; 1 = part#
        int cc=0;
        String dbInternal="";
        String dbExternal="";

        if  (z==0){
            dbInternal="partlink";
            dbExternal="[PartsLink #]";

        }if (z==1) {
            dbInternal="[part#]";
            dbExternal="[dnk #]";
        }

        //create statement object
        SQLUpdStm stm = new SQLUpdStm();

        String
                tableA, tableB,
                onA,onB,
                whereA,whereB,whereoperator,

                fieldA1,fieldB1,
                fieldA2,fieldB2,
                fieldA3,fieldB3,
                fieldA4,fieldB4,
                fieldA5,fieldB5,
                fieldA6,fieldB6,
                fieldA7,fieldB7,
                fieldA8,fieldB8,
                fieldA9,fieldB9,
                fieldA10,field10;
        int filesToUpdate=1;

        //the following will check for TX price update list and make everything ending Q, but not QQ set CAPA to true;
        //_costtygtx3 is the name of the update pricing files.   (This table should be used for CAPA pricing update from TYG_TX only
//        checkvendorQ("_costtygtx3");
        //end CAPA update




        while (filesToUpdate<=3){
    
    
            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                String dataSourceName = "dbLI";
                String dbURL = (new StringBuilder()).append("jdbc:odbc:").append(dataSourceName).toString();
                Connection conli = DriverManager.getConnection(dbURL, "", "");

    
                String st[] = new String[3];

                //***the following will check for TX price update list and make everything ending Q, but not QQ set CAPA to true;
                //_costtygtx3 is the name of the update pricing files.   (This table should be used for CAPA pricing update from TYG_TX only
                checkvendorQ("_costtygtx"+filesToUpdate);
                //***end CAPA update


    
    
                 //BC NO CAPA
                whereA="WHERE A.CAPA<>YES";
                whereB="B.CAPA<>YES";
                whereoperator="AND";
                tableA =    "tblbcn";
                tableB =    "_costTYGTX"+filesToUpdate;
                onA=        dbInternal;
                onB=        dbExternal;

//                fieldA1=    "oem_no";       //oem_no  (oem#)
//                fieldB1=    "[OE #]";

                fieldA2=    "[TYG_TX#]";       //Vendor Part #
                fieldB2=    "[TYGP #]";

                fieldA3=    "TYG_TX_P";      //Vendor Price to DNK
                fieldB3=    "[DNK $]";

    //            fieldA4=    "TX_PKG";        //There are no field for TX_PKG
    //            fieldB4=    "ROW";
                st[0] = stm.StmExecute(tableA,tableB,whereA,whereB,whereoperator,onA,onB ,fieldA2,fieldB2,fieldA3,fieldB3);
    //            System.out.println(st[0]);

                //BC WITH CAPA
                whereA="WHERE A.CAPA=YES";
                whereB="B.CAPA=YES";
                whereoperator="AND";
                tableA =    "tblbcn";
                tableB =    "_costTYGTX"+filesToUpdate;
                onA=        dbInternal;
                onB=        dbExternal;

//                fieldA1=    "oem_no";       //oem_no  (oem#)
//                fieldB1=    "[OE #]";

                fieldA2=    "[TYG_TX#]";       //Vendor Part #
                fieldB2=    "[TYGP #]";

                fieldA3=    "TYG_TX_P";      //Vendor Price to DNK
                fieldB3=    "[DNK $]";

                //            fieldA4=    "TX_PKG";        //There are no field for TX_PKG
                //            fieldB4=    "ROW";
                st[1] = stm.StmExecute(tableA,tableB,whereA,whereB,whereoperator,onA,onB ,fieldA2,fieldB2,fieldA3,fieldB3);
                //            System.out.println(st[0]);


                //TBLBCN
                whereA="";
                whereB="";
                whereoperator="";
                tableA =    "TBLCLN";
                tableB =    "_costTYGTX"+filesToUpdate;
                onA=        dbInternal;
                onB=        dbExternal;
    
//                fieldA1=    "oem_no";       //oem_no  (oem#)
//                fieldB1=    "[OE #]";
//    
                fieldA2=    "[TYG_TX#]";       //Vendor Part #
                fieldB2=    "[TYGP #]";
    
                fieldA3=    "TYG_TX_P";      //Vendor Price to DNK
                fieldB3=    "[DNK $]";
    
    //            fieldA4=    "TX_TYGPKG";   //Disable for Domestic purchases
    //            fieldB4=    "ROW";
                st[2] = stm.StmExecute(tableA,tableB,whereA,whereB,whereoperator,onA,onB ,fieldA2,fieldB2,fieldA3,fieldB3);
    //            System.out.println(st[1]);


                for (int x=0; x<st.length;x++){
                    Statement stli = conli.createStatement();

                    try {

//                        System.out.println("Script :"+x);
//                        System.out.println(st[x]);
                        int rowsupdated = stli.executeUpdate(st[x]);
                        //System.out.print("TRU :"+rowsupdated+"\t");
                        //JOptionPane.showOptionDialog(null, "Statement : " + st[x] + "\n\nTotal record Updated : " + rowsupdated, "Update Complete", JOptionPane.DEFAULT_OPTION, JOptionPane.INFORMATION_MESSAGE, null, new Object[]{}, null);
                        cc++;
                        if (cc==2){
                            //System.out.println("");
                            cc=0;
                        }


                    }catch(SQLException er) {
                        er.printStackTrace();
                    }stli.close();
                }filesToUpdate++;
            }
            catch(Exception e){
                e.printStackTrace();
            }
        }

    }
    public void checkvendorQ(String tygtxPriceList){
        {
            int x=0;
            int y=0;
            try
            {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                String dataSourceName = "dbLI";
                String dbURL = (new StringBuilder()).append("jdbc:odbc:").append(dataSourceName).toString();
                Connection conli = DriverManager.getConnection(dbURL, "", "");

//            Statement stli = conli.createStatement();
//            stli.execute("UPDATE  tblinventory SET CAPA=true" +
//                    " where [Part#] like '%Q' and [Part#] NOT LIKE '%QQ';");

                String st[] = new String[1];
                st[0] = ("UPDATE  ["+tygtxPriceList+"] SET CAPA=true" +
                        " where [tygp #] like '%Q' and [tygp #] NOT LIKE '%QQ';");            //verify if this really works

//            st[0] = ("UPDATE  tblinventory SET CAPA=true" +
//                    " where [Part#] like '%Q' and [Part#] NOT LIKE '%QQ';");


                for (x=0; x<st.length;x++){
                    Statement stli = conli.createStatement();
                    try {
//                        System.out.println(st[x]);
                        int rowsupdated = stli.executeUpdate(st[x]);
                        //System.out.println("TRU ChkVend: "+rowsupdated);


                    }catch(SQLException er) {
                        er.printStackTrace();
                        JOptionPane.showMessageDialog(null, er);
                    }stli.close();
                }
            }
            catch(Exception e)
            {
                e.printStackTrace();
                JOptionPane.showMessageDialog(null,e);
            }
        }

    }
    
}